﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class Main_Reports_Inventory_TestStockReport : System.Web.UI.Page
{
    Datautility DU = new Datautility(); DataTable Dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetData();
        }
    }
    protected void grdStockReport_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdStockReport.PageIndex = e.NewPageIndex;
        GetData();
    }
    protected void btngetreport_Click(object sender, EventArgs e)
    {
        GetData();
    }
    protected void grdStockReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //if (e.Row.RowType == DataControlRowType.DataRow)
        //{
        //    Label lblPurchaseQty = (Label)e.Row.FindControl("lblPurchaseQty");
        //    if (lblPurchaseQty.Text != "0.0")
        //    {
        //        e.Row.BackColor = System.Drawing.Color.Red;                
        //    }
        //}
    }
    protected void GetData()
    {
        try
        {
            string todate = "";
            string fromdate = "";
            DataTable dtstockDetail = DU.getdatausingprocedure("Usp_IVR_GetStockDetails");
            Dt.Columns.Add("ITMID", typeof(Int32));
            Dt.Columns.Add("ItemCode", typeof(string));
            Dt.Columns.Add("ItemName", typeof(string));
            Dt.Columns.Add("PurchaseQty", typeof(string));
            Dt.Columns.Add("PurchaseValue", typeof(string));
            Dt.Columns.Add("IssueQty", typeof(string));
            Dt.Columns.Add("IssueValue", typeof(string));
            Dt.Columns.Add("SingleValue", typeof(string));
            Dt.Columns.Add("OpeningBalQty", typeof(string));
            Dt.Columns.Add("OpeningBalValue", typeof(string));
            Dt.Columns.Add("ClosingQty", typeof(string));
            Dt.Columns.Add("ClosingValue", typeof(string));
            Dt.Columns.Add("LeasedType", typeof(string));
            Dt.Columns.Add("LandedCost", typeof(decimal));
            Dt.Columns.Add("InventoryClass", typeof(string));

            double TempItemReturnValue = 0; double TempPurchaseReturnQty = 0;
            double TempOpeningIssueQty = 0; double TempOpeningValue = 0; double TempPurValue = 0;
            double TempOpeningRetrun = 0; double TempPurQty = 0; double TempValue = 0;
            double TempClosingValue = 0; double TempItemReturnQty = 0;
            double TempClosingQty = 0; double TempOpeningIssueValue = 0;
            double TempIssueQty = 0; double TempPurIssueValue = 0;
            double TempBalanceQty = 0; double singleqtyValue = 0;
            double TempLandedCost = 0; double PurchaseSingleValue = 0;
            double TempPurQtyclosing = 0; double TempRemaining = 0;
            if (dtstockDetail.Rows.Count > 0)
            {                
                if (txttodate.Text != "")
                {
                    todate = Convert.ToDateTime(txttodate.Text).ToString(string.Format("yyyy/MM/dd"));
                }
                else
                {
                    todate = System.DateTime.Now.ToString("yyyy/MM/dd");
                }
                if (txtfromdate.Text != "")
                {
                    fromdate = Convert.ToDateTime(txtfromdate.Text).ToString(string.Format("yyyy-MM-dd"));
                }
                //else
                //{
                //    fromdate = dtstockDetail.Rows[i]["OPDATE"].ToString();
                //}
                lblRecordNo.Text="To Date"+ todate;
                lblperiod.Text = "From Date" + fromdate;
                for (int i = 0; i < dtstockDetail.Rows.Count; i++)
                {                   
                    DataRow DR = Dt.NewRow();                    
                    DR["ItemCode"] = dtstockDetail.Rows[i]["ItemCode"];                   
                    string Openingstock = "select ISNULL(CONVERT(decimal(18,0),Quantity),0) - ISNULL(CONVERT(decimal(18,0),IssueQty ),0) + ISNULL(CONVERT(decimal(18,0),RetrunQty ),0) as BalanceQty,ISNULL(RetrunQty,0) as RetrunQty,ISNULL(Quantity,0) as Quantity,ISNULL(IssueQty,0) as IssueQty,AMOUNT,RATE from IVR_ITEMOPENBAL where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and OPDATE = '2014-04-01'";
                    string Purchase = "select ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.ITMQTY),0)- ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.IssueQty),0) as BalanceQTY, IVR_TRAN_ITEM.ITMQTY as ITMQTY,IVR_TRAN_ITEM.IssueQty as IssueQty,IVR_TRAN_ITEM.RATE as RATE,ISNULL(IVR_TRAN_ITEM.ITMQTY,0)* ISNULL(IVR_TRAN_ITEM.RATE,0) as Value, GROSSAMT as LandedCost from IVR_TRAN_ITEM inner join IVR_TRAN_MAIN on IVR_TRAN_MAIN.TRNID=IVR_TRAN_ITEM.TRNID where IVR_TRAN_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_TRAN_ITEM.EntryType='PUR' and IVR_TRAN_MAIN.PurchaseRecDate Between '2014-04-01' and '" + todate + "'";
                    string IssueItem = "select ISNULL(IVR_OnlineRegistration_Detail.IssueQty,0) as IssueQty from IVR_OnlineRegistration_Detail inner join IVR_ITEM on IVR_OnlineRegistration_Detail.ItemCode=IVR_ITEM.ITMCODE inner join IVR_ITEMOPENBAL on IVR_ITEMOPENBAL.ITMID=IVR_ITEM.ITMID where IVR_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_OnlineRegistration_Detail.IssuedDate between '2014-04-01' and '" + todate + "'";
                    string ReturnItem = "select ISNULL(returnQty,0) as RETURNQTY from IVR_OnlineRegistration_Detail inner join IVR_ITEM on IVR_OnlineRegistration_Detail.ItemCode=IVR_ITEM.ITMCODE where IVR_ITEM.ITMCODE='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_OnlineRegistration_Detail.ReturnDate between '2014-04-01' and '" + todate + "'";
                   
                    DataTable DtOpening = DU.getdata(Openingstock);
                    DataTable DTPurchase = DU.getdata(Purchase);
                    DataTable DTIssueItem = DU.getdata(IssueItem);
                    DataTable DTReturnItem = DU.getdata(ReturnItem);
                    
                    if (DtOpening.Rows.Count > 0)
                    {
                        TempBalanceQty = Convert.ToDouble(DtOpening.Rows[0]["Quantity"].ToString());
                        TempOpeningValue = Convert.ToDouble(DtOpening.Rows[0]["BalanceQty"].ToString()) * Convert.ToDouble(DtOpening.Rows[0]["RATE"].ToString());                    
                    }
                    if (TempOpeningValue > 0)
                    {
                        singleqtyValue = (TempOpeningValue / TempBalanceQty);
                    }
                    //Label1.Text = singleqtyValue.ToString() + ",";
                    ////From the Purchase Receive
                    if (DTPurchase.Rows.Count > 0)
                    {
                        for (int j = 0; j < DTPurchase.Rows.Count; j++)
                        {                    
                            TempPurValue += Convert.ToDouble(DTPurchase.Rows[j]["Value"].ToString());                    
                            TempPurQty += Convert.ToDouble(DTPurchase.Rows[j]["ITMQTY"].ToString());                    
                        }
                    }
                    if (TempPurValue > 0)
                    {
                        PurchaseSingleValue=(TempPurValue/TempPurQty);
                    }
                    if (DTIssueItem.Rows.Count > 0)
                    {
                        for (int k = 0; k < DTIssueItem.Rows.Count; k++)
                        {
                            TempIssueQty += Convert.ToDouble(DTIssueItem.Rows[k]["IssueQty"].ToString());
                        }
                        if (TempIssueQty > TempBalanceQty)
                        {
                            TempRemaining = TempIssueQty - TempBalanceQty;
                            TempValue = TempRemaining * PurchaseSingleValue;
                            TempPurIssueValue = TempValue + TempOpeningValue;
                        }
                        if (TempIssueQty <= TempBalanceQty)
                        {
                            TempValue = TempIssueQty * singleqtyValue;
                            TempPurIssueValue = TempValue;
                        }
                    }
                    if (DTReturnItem.Rows.Count > 0)
                    {
                        for (int q = 0; q < DTReturnItem.Rows.Count; q++)
                        {
                            TempPurchaseReturnQty += Convert.ToDouble(DTReturnItem.Rows[q]["RETURNQTY"].ToString());                            
                        }
                        TempItemReturnValue = (TempOpeningIssueValue / TempBalanceQty) * TempPurchaseReturnQty;                        
                    }
                    //#endregion
                    //// for closing quantiy between two date. calculataion is written as follows...
                    //#region for Closing Operation
                    //string closingstock = "select ISNULL(CONVERT(decimal(18,0),Quantity),0) - ISNULL(CONVERT(decimal(18,0),IssueQty ),0) + ISNULL(CONVERT(decimal(18,0),RetrunQty ),0) as BalanceQty,ISNULL(RetrunQty,0) as RetrunQty,ISNULL(Quantity,0) as Quantity,ISNULL(IssueQty,0) as IssueQty,AMOUNT,RATE from IVR_ITEMOPENBAL where ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and OPDATE  Between '" + fromdate + "' and '"+todate+"'";
                    //string Purchaseclosing = "select ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.ITMQTY),0)- ISNULL(CONVERT(decimal(18,0),IVR_TRAN_ITEM.IssueQty),0) as BalanceQTY, IVR_TRAN_ITEM.ITMQTY as ITMQTY,IVR_TRAN_ITEM.IssueQty as IssueQty,IVR_TRAN_ITEM.RATE as RATE,ISNULL(IVR_TRAN_ITEM.ITMQTY,0)* ISNULL(IVR_TRAN_ITEM.RATE,0) as Value, GROSSAMT as LandedCost from IVR_TRAN_ITEM inner join IVR_TRAN_MAIN on IVR_TRAN_MAIN.TRNID=IVR_TRAN_ITEM.TRNID where IVR_TRAN_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_TRAN_ITEM.EntryType='PUR' and IVR_TRAN_MAIN.PurchaseRecDate Between '" + fromdate + "' and '" + todate + "'";
                    //string PurchaseClosingReturn = "Select IVR_TRAN_ITEM.ReturnQty as ReturnQty from IVR_TRAN_ITEM inner join IVR_TRAN_MAIN on IVR_TRAN_MAIN.TRNID=IVR_TRAN_ITEM.TRNID where IVR_TRAN_ITEM.ITMID='" + dtstockDetail.Rows[i]["ITMID"] + "' and IVR_TRAN_ITEM.Entrytype='RET' and IVR_TRAN_MAIN.PurchaseRecDate Between '" + fromdate + "' and '" + todate + "'";

                    
                    //#region Adding Data into the DataRow 
                    DR["SingleValue"] = singleqtyValue;
                    DR["OpeningBalQty"] = TempBalanceQty; //+ TempPurchaseQty;
                    DR["OpeningBalValue"] = TempOpeningValue.ToString("N2");// + TempPurValue; //OpeningValue + "=[Opening Value=" + TempOpeningValue + "+ Purchase Value=" + TempPurValue + "]";

                    DR["PurchaseQty"] = TempPurQty; //+ "=[Purchase Qty]";
                    DR["PurchaseValue"] = TempPurValue.ToString("N2");// + "=[Purchase Value]";

                    DR["IssueQty"] = TempIssueQty; //+ "=[OpeningIssue=" + TempOpeningIssueQty + "+ PurchaseIssue=" + TempIssueQty + "]";
                    DR["IssueValue"] = TempPurIssueValue.ToString("N2"); //OpeningValue + "=[Opening Value=" + TempOpeningValue + "+PurchaseValue=" + TempPurValue + "]";                    

                    DR["ClosingQty"] = (TempBalanceQty + TempPurQty + TempPurchaseReturnQty) - TempIssueQty;// +(TempOpeningRetrun + TempPurchaseReturnQty);
                    DR["ClosingValue"] = ((TempOpeningValue + TempPurValue + TempItemReturnValue) - TempPurIssueValue).ToString("N2");
                    
                    //DR["LandedCost"] = TempLandedCost + TempOpeningValue;

                    //if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) > 500)
                    //{
                    //    DR["InventoryClass"] = "A";
                    //}
                    //else if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) >= 100 || (TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) <= 499)
                    //{
                    //    DR["InventoryClass"] = "B";
                    //}
                    //else if ((TempBalanceQty + TempPurchaseQty + TempClosingQty + TempPurQtyclosing) <= 99)
                    //{
                    //    DR["InventoryClass"] = "C";
                    //}
                    //#endregion
                    //Empty Data.
                    //#region Empty the Variable
                    TempItemReturnValue = 0; TempPurchaseReturnQty = 0;
                    TempOpeningIssueQty = 0; TempOpeningValue = 0; TempPurValue = 0;
                    TempClosingQty = 0; TempIssueQty = 0; singleqtyValue = 0;
                    TempBalanceQty = 0; TempLandedCost = 0; TempPurQty = 0; TempClosingValue = 0;
                    TempOpeningRetrun = 0; TempPurQtyclosing = 0; TempValue = 0;
                    TempOpeningIssueValue = 0; TempPurIssueValue = 0;TempItemReturnQty=0;
                    //#endregion
                    Dt.Rows.Add(DR);
                }
                grdStockReport.DataSource = Dt;
                //grdStockReport.AllowPaging = true;
                grdStockReport.DataBind();
                lblRecordNo.Text = grdStockReport.Rows.Count.ToString() + " Record(s)";
                lblperiod.Text = Convert.ToDateTime(fromdate).ToString(string.Format("dd-MM-yyyy")) +" to " + Convert.ToDateTime(todate).ToString(string.Format("dd-MM-yyyy"));
            }
            fromdate = ""; todate = "";
        }
        catch (Exception ex)
        {
            lblperiod.Text = ex.Message;
        }
    }
}